<?php

namespace App\Model;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

use App\Providers\VinServiceProvider;

class CarVin extends Model
{

    /**
     * 关联到模型的数据表
     *
     * @var string
     */
    protected $table = 'car_vin';

    public static function info( $vin ){

        $sql = "select *
                from car_vin v 
                where v.vin = ? ";
        $data = self::where('vin', $vin)->first();

        $vinInfo = !empty( $data ) ? $data->toarray() : [];

        if( empty( $vinInfo ) ){
            //todo 到龙蟠抓数据
            $vinInfo_lopal = VinServiceProvider::lopalInfo( $vin );
            $vinInfo_lopal_json = '';
            if( !empty( $vinInfo_lopal ) ){
                $preg = '/[\d]{4}/';
                preg_match_all( $preg, $vinInfo_lopal['year'], $matches);
                $vinInfo_lopal['year'] = implode(',', $matches[0] );
                $vinInfo_lopal_json = json_encode( $vinInfo_lopal );
            }

            // 到领航抓取
            $vinInfo = VinServiceProvider::yhshInfo( $vin );
            if( !empty( $vinInfo ) ){

                $vinInfo['info'] = '';
                $vinInfo['info_lopal'] = $vinInfo_lopal_json;
                $vinInfo['source'] = 'linghang';
                $vinInfo['vin'] = $vin;
                $vinInfo['year'] = !empty($vinInfo_lopal['year']) ? $vinInfo_lopal['year'] : $vinInfo['year'];
                $vinInfo['gearbox'] = !empty($vinInfo_lopal['gearbox']) ? $vinInfo_lopal['gearbox'] : $vinInfo['gearbox'];
                CarVin::insertLinghangData( $vinInfo );
            }else{

                $vinInfo = VinServiceProvider::info( $vin );
                $extends_data = [
                    'year'=>!empty( $vinInfo_lopal['year'] ) ? $vinInfo_lopal['year'] : '',
                    'gearbox'=>!empty( $vinInfo_lopal['gearbox'] ) ? $vinInfo_lopal['gearbox'] : ''
                ];
                if( $vinInfo['status'] == 0) {
                    $vinInfo['vin'] = $vin;
                    $vinInfo['source'] = 'juhe';
                    $vinInfo = CarVin::insertJuheData([
                        'info' => $vinInfo['info'],
                        'info_lopal' => $vinInfo_lopal_json,
                        'vin' => $vin,
                        'source' => 'juhe',
                    ], $extends_data);
                }else if( !empty($vinInfo_lopal) ){
                    $vinInfo = CarVin::insertLopalData([
                        'info' => '',
                        'info_lopal' => $vinInfo_lopal_json,
                        'vin' => $vin,
                        'source' => 'lopal',
                    ], $vinInfo_lopal);
                    $vinInfo['source']='lopal';
                }else{
                    $vinInfo = [];
                }
            }


        }
        return $vinInfo ;
    }

    /**
     * info 方法的改进型， 因聚合数据接口变动而改变
     * @param $vin
     * @return array
     */
    public static function info_new( $vin ){

        $sql = "select *
                from car_vin v
                where v.vin = ? ";
        $data = self::where('vin', $vin)->first();

        $vinInfo = !empty( $data ) ? $data->toarray() : [];

        if( empty( $vinInfo ) ){
            //todo 到龙蟠抓数据（优先级最高）
            $vinInfo_lopal = VinServiceProvider::lopalInfo( $vin );
            $vinInfo_lopal_json = '';
            if( !empty( $vinInfo_lopal ) ){
                $preg = '/[\d]{4}/';
                preg_match_all( $preg, $vinInfo_lopal['year'], $matches);
                $vinInfo_lopal['year'] = implode(',', $matches[0] );
                $vinInfo_lopal_json = json_encode( $vinInfo_lopal );

                $vinInfo = CarVin::insertLopalData([
                    'info' => '',
                    'info_lopal' => $vinInfo_lopal_json,
                    'vin' => $vin,
                    'source' => 'lopal',
                ], $vinInfo_lopal);
                $vinInfo['source']='lopal';

            }else{
                // 到聚合新接口抓取数据 （优先级次高）
                $vinInfo = VinServiceProvider::juhe2Info( $vin );
                if( $vinInfo['status'] == 0) {
                    $vinInfo = CarVin::insertJuhe2Data([
                        'info' => $vinInfo['info'],
                        'info_lopal' => $vinInfo_lopal_json,
                        'vin' => $vin,
                        'source' => 'juhe2',
                    ]);
                }else{
                    // 到领航抓取数据 （优先级最低）
                    $vinInfo = VinServiceProvider::yhshInfo( $vin );
                    if( !empty( $vinInfo ) ){
                        $vinInfo['info'] = '';
                        $vinInfo['info_lopal'] = $vinInfo_lopal_json;
                        $vinInfo['source'] = 'linghang';
                        $vinInfo['vin'] = $vin;
                        CarVin::insertLinghangData( $vinInfo );
                    }else{
                        $vinInfo = [];
                    }

                }
            }

        }
        return $vinInfo ;
    }

    /**
     * 领航新的抓取方式， 领航网站改版以后
     * 多了一个中间页
     */
    public static function info_linghang( $vin ){

        $sql = "select *
                from car_vin v
                where v.vin = ? ";
        $data = self::where('vin', $vin)->first();

        $vinInfo = !empty( $data ) ? $data->toarray() : [];

        if( empty( $vinInfo ) ){
            $vinInfo_linghang = VinServiceProvider::yhshInfo_middle( $vin );

            if( !empty( $vinInfo_linghang ) ){
                $vinInfo_linghang['info'] = '';
                $vinInfo_linghang['info_lopal'] = '';
                $vinInfo_linghang['source'] = 'linghang';
                $vinInfo_linghang['vin'] = $vin;
                CarVin::insertLinghangData( $vinInfo_linghang );
                $vinInfo = $vinInfo_linghang;
            }
        }
        return $vinInfo;

    }

    public static function insertData( $data ){

        $now = date('Y-m-d H:i:s');
        $sql = "insert into car_vin
                set vin=?,info=?,source=?,created_at='$now',updated_at='$now' ";
        $res = DB::insert($sql,
            [ $data['vin'], $data['info'], $data['source'] ]);
        return $res ;
    }

    public static function insertJuheData( $data, $extends_data=array() ){

        $preg = '/(.*?)\s(.*?)\s(.*厢)\s(\d{4}款)\s([\d.]+L)\s(.*?)\s([\d.\w]+)\s(.*?)(\s.*)?$/';
        preg_match( $preg, $data['info'], $matches );
        $model_name_string = $matches[3];

        $model_name_split = explode('（', $model_name_string);
        $model_name = $model_name_split[0];

        preg_match('/（(.*)）/', $model_name_string, $model_type_split );
        $model_type = $model_type_split[1];

        $year = substr($matches[4],0,4);
        $engine_displacement = str_replace( 'L', '', $matches[5] );
        $gearbox = $matches[8];
        $info_arr = [
            'brand_name'    => $matches[2],
            'factory_name'  => $matches[1],
            'model_name'    => $model_name,
            'model_type'    => $model_type,
            'year'          => !empty($extends_data['year']) ? $extends_data['year'] : $year,
            'engine'        => $engine_displacement,
            'gearbox'       => !empty($extends_data['gearbox']) ? $extends_data['gearbox'] : $gearbox,
        ];
        $data = array_merge($data, $info_arr);
        $now = Date("Y-m-d H:i:s");
        $sql = "insert into car_vin
                set vin=?,
                info=?,
                info_lopal=?,
                brand_name=?,
                factory_name=?,
                model_name=?,
                model_type=?,
                engine=?,
                `year`=?,
                gearbox=?,
                source=?,
                created_at='$now',updated_at='$now'";
        DB::insert($sql,
            [
                $data['vin']
                ,$data['info']
                ,$data['info_lopal']
                ,$data['brand_name']
                ,$data['factory_name']
                ,$data['model_name']
                ,$data['model_type']
                ,$data['engine']
                ,$data['year']
                ,$data['gearbox']
                ,$data['source']
            ]);
        return $data ;
    }

    public static function insertJuhe2Data( $data ){
        $now = date('Y-m-d H:i:s');
        $info = [];
        $all_info = $data['info'];
        $resData = [];

        if( !empty($all_info ) ){
            $resData['brand_name'] = '';
            $info = $all_info[0];
//            $groupName = preg_replace("/\\(.*\\)/",'', $info['groupName']);
            //todo 获取数据库所有的品牌
            $allBrand = CarBrand::all();

            //todo 整理出数据$data 的brand_name
//            $brand_name = '';
            $selected_brand = [];
            foreach($allBrand as $brand ){
                if(strpos( $info['brandName'], $brand->name ) !== false ){
                    $resData['brand_name'] = $brand->name;
                    $selected_brand = $brand;
                    break;
                }
            }

            //todo 整理出数据$data 的factory_name
            $selected_factory = [];
            $resData['factory_name'] = '';
            $split_factory = '';
            if( !empty( $selected_brand ) ){
                $split_factory = str_replace( $selected_brand->name, '', $info['brandName']);

                $sql = "select * from car_factory where `name` like '%". $split_factory ."%'"
                    . " and `brand_id`=" .$selected_brand->id;
                $factories = DB::select( $sql );
                if( !empty( $factories ) ){
                    $selected_factory = $factories[0] ;
                    $resData['factory_name'] = $selected_factory->name;
                }else{
                    $resData['factory_name'] = $split_factory;
                }


            }

            //todo 整理出数据$data 的model_name
            $split_factory = str_replace('上汽', '上海', $split_factory);
            $selected_model_name = str_replace($split_factory,'', $info['familyName']);
            $split_factory = str_replace('上海', '上汽', $split_factory);
            $selected_model_name = str_replace($split_factory, '', $selected_model_name);
            $selected_model_name = str_replace($selected_brand->name, '', $selected_model_name);
            $resData['model_name'] = $selected_model_name;
            $resData['model_type'] = $selected_model_name;
            //todo 整理出数据的所有年份
//            $all_year = [];
//            foreach( $all_info as $vehicle ){
//
//                if( !in_array($vehicle['yearPattern'], $all_year) ){
//                    $all_year[] = $vehicle['yearPattern'];
//                }
//
//            }
//            $resData['year'] = implode(',', $all_year);
            $resData['year'] = $info['yearPattern'];
            //todo 整理变速箱类型
            $resData['gearbox'] = str_replace('档','',$info['gearboxType']);
            $resData['gearbox_lv'] = $info['gearNum'];
            $resData['engine'] = $info['displacement'] . 'L';
            $resData['engine'] .= !empty($info['fuelJetType']) ? ' '. $info['fuelJetType'] : '';
            $resData['engine'] .= !empty($info['engineModel']) ? ' '. $info['engineModel'] : '';
            $resData['vin'] = $data['vin'];
            $resData['source'] = $data['source'];

            //todo 整理出数据的所有变速箱类型，如果api 数据提供多个数据的情况下
            self::insertJuhe2Extra( $resData, $all_info );
        }
        $res = [];
        if( !empty( $resData ) ){
            $sql = "insert into car_vin
                set vin=?,
                info=?,
                info_lopal=?,
                brand_name=?,
                factory_name=?,
                model_name=?,
                model_type=?,
                engine=?,
                `year`=?,
                gearbox=?,
                gearbox_lv=?,
                source=?,
                created_at='$now',updated_at='$now' ";
            $res = DB::insert($sql,
                [
                    $resData['vin']
                    ,json_encode($data['info'], JSON_UNESCAPED_UNICODE)
                    ,$data['info_lopal']
                    ,$resData['brand_name']
                    ,$resData['factory_name']
                    ,$resData['model_name']
                    ,$resData['model_type']
                    ,$resData['engine']
                    ,$resData['year']
                    ,$resData['gearbox']
                    ,$resData['gearbox_lv']
                    ,$resData['source']
                ]);
        }

        return $resData ;
    }

    /**
     * 根据year,gearbox 进行分组
     * @param $vin
     */
    public static function getJuhe2ExtraByGroup( $vin ){

        $sql = 'select cvfj2.*, co.img_url as oil_img_url , co.name as oil_name from car_vin_from_juhe2 cvfj2 '
                . ' left join car_oil co on cvfj2.oil_id=co.id '
                . ' where vin=? group by cvfj2.`year`,cvfj2.gearbox';
        $res = DB::select( $sql, [$vin] );
        return $res;
    }
    /**
     * $list 长度不超过1，什么都不做
     */
    public static function insertJuhe2Extra( $vinInfo, $list ){
        if( count( $list ) > 1 ) {
            foreach( $list as $single ){
                $likedData = [
                    'year'=> $single['yearPattern'],
                    'gearbox'=> $single['gearboxType'],
                    'brand_name'=> $vinInfo['brand_name'],
                    'model_name'=> $vinInfo['model_name'],
                    'engine'=> ''  // api数据的发动机排量太精确，无法匹配
                ];
                $objectInfo = self::findLikedObjectInfo( $likedData );
                $oil_id = 0;
                if( !empty( $objectInfo ) ){

                    if( empty( $objectInfo->oil_id ) ){
                        $new_oil = CarOil::getOilByRule( $objectInfo->mi_key, $objectInfo->oil );
                        if( !empty( $new_oil ) ){
                            $oil_id = $new_oil['id'];
                            //todo 如果表 car_model_object_info 中的oil_id 为空，插入该值
                            CarModelObjectInfo::setOilId( $objectInfo->mi_key, $new_oil['id']);
                        }
                    }else{
                        $oil_id = $objectInfo->oil_id;
                    }
                    //todo 如果表 car_vin 中的oil_id 为空，插入该值

                }

                $sql = "insert into car_vin_from_juhe2 set vin=?,info=?,`year`=?,gearbox=?,gearbox_lv=?,oil_id=?,updated_at=now(),created_at=now() ";
                DB::insert($sql,
                    [
                        $vinInfo['vin']
                        ,json_encode($single,JSON_UNESCAPED_UNICODE)
                        ,$single['yearPattern']
                        ,$single['gearboxType']
                        ,$single['gearNum']
                        ,$oil_id
                    ]);
            }
        }
    }

    public static function insertLinghangData( $data ){

        $now = date('Y-m-d H:i:s');
        $sql = "insert into car_vin
                set vin=?,
                info=?,
                info_lopal=?,
                factory_name=?,
                model_name=?,
                model_type=?,
                engine=?,
                `year`=?,
                gearbox=?,
                gearbox_lv=?,
                oil=?,
                oil_regional=?,
                oil_change=?,
                oil_img_url=?,
                source=?,
                created_at='$now',updated_at='$now' ";
        $res = DB::insert($sql,
            [
                $data['vin']
                ,$data['info']
                ,$data['info_lopal']
                ,$data['factory_name']
                ,$data['model_name']
                ,$data['model_type']
                ,$data['engine']
                ,$data['year']
                ,$data['gearbox']
                ,$data['gearbox_lv']
                ,$data['oil']
                ,$data['oil_regional']
                ,$data['oil_change']
                ,$data['oil_img_url']
                ,$data['source']
            ]);
        return $res ;
    }

    public static function insertLopalData( $data,$extends_data ){

        $now = date('Y-m-d H:i:s');
        $sql = "insert into car_vin
                set vin=?,
                info=?,
                info_lopal=?,
                brand_name=?,
                factory_name=?,
                model_name=?,
                model_type=?,
                `year`=?,
                gearbox=?,
                oil=?,
                oil_regional=?,
                oil_change=?,
                source=?,
                created_at='$now',updated_at='$now' ";
        $res = DB::insert($sql,
            [
                $data['vin']
                ,$data['info']
                ,$data['info_lopal']
                ,$extends_data['brand_name']
                ,$extends_data['factory_name']
                ,$extends_data['model_name']
                ,$extends_data['model_type']
                ,$extends_data['year']
                ,$extends_data['gearbox']
                ,$extends_data['oil']
                ,$extends_data['oil_regional']
                ,$extends_data['oil_change']
                ,$data['source']
            ]);
        return $extends_data ;
    }

    public static function edit( $vin , $data ){

        $car_vin = self::where( 'vin', $vin )->first();

        foreach( $data as $key => $value ){
            $car_vin->$key = $value;
        }
        return $car_vin->save();
    }

    /**
     * 对润滑油相关属性进行编辑， 如果这些属性没有值的情况下再改变
     * @param $vin
     * @param $data
     */
    public static function editOil( $vin, $data ){

        $sql = "update car_vin set 
                gearbox_lv=?,
                oil_id=?,
                oil_regional=?,
                oil_change=? where vin='$vin'";
        DB::update( $sql, [
            $data['gearbox_lv'],
            $data['oil_id'],
            $data['oil_regional'],
            $data['oil_change'],
        ] );
    }

    /**
     * 找到同样的object_info
     * 适用于领航数据
     */
    public static function findSameObjectInfo( $car_vin ){

        $car_vin['year'] = explode(',',$car_vin['year'])[0];
        $sql = 'select moi.* from car_model_object_info moi
                left join car_model_object mo on moi.mi_key=mo.mi_key
                left join car_model m on m.id=mo.model_id
                left join car_factory f on f.id=m.factory_id
                where f.name=? '
                .' and m.name=? '
//                .' and moi.model_type=? '
            .' and moi.engine=? '
            .' and moi.year=? '
            .' and moi.gearbox=? '
            .' and moi.gearbox_lv=?'
            .' and moi.oil=? '
            .' and moi.oil_regional=? '
            .' and moi.oil_change=? ';
        $res = DB::select( $sql, [
            $car_vin['factory_name']
            ,$car_vin['model_name']
//            ,$car_vin['model_type']
            ,$car_vin['engine'],$car_vin['year']
            ,$car_vin['gearbox'],$car_vin['gearbox_lv'],$car_vin['oil']
            ,$car_vin['oil_regional'],$car_vin['oil_change']] );
        if( !empty( $res ) ){
            return $res[0];
        }else{
            //如果匹配不到，弱化条件（为龙蟠着想）
            $sql = 'select moi.* from car_model_object_info moi
                left join car_model_object mo on moi.mi_key=mo.mi_key
                left join car_model m on m.id=mo.model_id
                left join car_factory f on f.id=m.factory_id
                where f.name=? '
                .' and m.name=? '
                .' and moi.year=? '
                .' and moi.gearbox=? ' ;
            $res = DB::select( $sql, [
                $car_vin['factory_name']
                ,$car_vin['model_name']
                ,$car_vin['year']
                ,$car_vin['gearbox']
            ] );
        }
        return !empty( $res ) ? $res[0] : [];
    }

    /**
     * 找到相似的object_info
     * 适用于聚合API数据
     */
    public static function findLikedObjectInfo( $car_vin ){
        $year_arr = explode(',',$car_vin['year']);
        $where_year = '';
        foreach( $year_arr as $year ){
            if( $where_year == '' ){
                $where_year .= ' moi.year="' . $year . '"';
            }else{
                $where_year .= ' or moi.year="' . $year . '"';
            }
        }
        if( $where_year != '' ){
            $where_year = '(' . $where_year . ')';
        }

        $car_vin['year'] = explode(',',$car_vin['year'])[0];
        $sql = 'select moi.* from car_model_object_info moi
                left join car_model_object mo on moi.mi_key=mo.mi_key
                left join car_model m on m.id=mo.model_id
                left join car_factory f on f.id=m.factory_id
                left join car_brand b on b.id=f.brand_id '.
                ' where '
            .' b.name=? '
            .' and m.name=? '
            .' and moi.engine like "'. $car_vin['engine'] .'%" '
            .' and '.$where_year
//            .' and moi.year=? '
            .' and moi.gearbox like "'. $car_vin['gearbox'] .'%" '
            ;
        $res = DB::select( $sql, [
            $car_vin['brand_name']
            ,$car_vin['model_name']
//            ,$car_vin['engine']
            ,$car_vin['year']
//            ,$car_vin['gearbox']
        ] );
        if( !empty( $res ) ){
            return $res[0];
        }else{
            //如果匹配不到，弱化条件（为龙蟠着想）
            $sql = 'select moi.* from car_model_object_info moi
                left join car_model_object mo on moi.mi_key=mo.mi_key
                left join car_model m on m.id=mo.model_id
                left join car_factory f on f.id=m.factory_id
                left join car_brand b on b.id=f.brand_id '.
                ' where '
                .' b.name=? '
//                .' and ? like concat("%",m.name,"%") '
                .' and (? like concat("%",replace(m.`name`,"级",""),"%") '
                .' or moi.model_type like "%'. $car_vin['model_name'] .'%" )'
                .' and '.$where_year
//                .' and moi.year=? '
                .' and moi.gearbox like "'. $car_vin['gearbox'] .'%" '
            ;
            $res = DB::select( $sql, [
                $car_vin['brand_name']
                ,$car_vin['model_name']
//                ,$car_vin['year']
            ] );
        }
        return !empty( $res ) ? $res[0] : [];

    }
}